package cn.com.dashihui.web.service;

import java.sql.SQLException;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.shiro.authc.credential.DefaultPasswordService;
import org.apache.shiro.authc.credential.PasswordService;

import com.jfinal.aop.Before;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.IAtom;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;

import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.dao.Admin;

public class AdminService {
	
	public boolean findExistsByUsername(String username){
		return Admin.me().findFirst("SELECT * FROM t_auth_admin WHERE username=?",username)!=null;
	}
	
	public boolean addUser(Admin newObject){
		return newObject.save();
	}
	
	public boolean delUser(final int id){
		//事务处理，同时删除用户记录，并删除相应的用户角色关联记录
		return Db.tx(new IAtom(){
			@Override
			public boolean run() throws SQLException {
				boolean ret1 = Admin.me().deleteById(id);
				Db.update("DELETE FROM t_auth_admin_roles WHERE userid=?",id);
				return ret1;
			}
		});
	}
	
	/**
	 * 查询分页数据
	 * @param pageNum
	 * @param pageSize
	 * @return
	 */
	public Page<Record> findByTotle(int pageNum, int pageSize,String beginDate,String endDate){
	

		
		StringBuffer sqlExcept = new StringBuffer("FROM t_obj_date td left join (select ifnull(count(*),0) as usNum,createDate from t_bus_user group by DATE_FORMAT(createDate,'%Y-%m-%d')) tu on td.RepDate = tu.createDate "
				+" where 1=1 ");
    	if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
    		sqlExcept.append("AND td.RepDate BETWEEN"+"'"+beginDate+"'"+ "AND "+"'" + endDate+"'");
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sqlExcept.append("AND DATE_FORMAT(td.RepDate,'%Y-%m-%d')>="+"'"+beginDate+"'");
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sqlExcept.append("AND DATE_FORMAT(td.RepDate,'%Y-%m-%d')<="+"'"+endDate+"'");
		}
		sqlExcept.append(" order by td.RepDate desc");
		return Db.paginate(pageNum, pageSize, "select *", sqlExcept.toString());
	
	}
	
	public boolean editUser(Admin object){
		return object.update();
	}
	
	@Before(Tx.class)
	public boolean editMinePwd(Admin currentUser,String passwordOld,String passwordNew) throws Exception{
		Admin user = Admin.me().findById(currentUser.getInt("id"));
		if(user==null){
			return false;
		}
		PasswordService svc = new DefaultPasswordService();
		if(!svc.passwordsMatch(passwordOld, user.getStr("password"))){
			throw new Exception("原密码输入不正确");
		}else{
			return user.set("password", svc.encryptPassword(passwordNew)).update();
		}
	}
	
	public Admin findEnabledByUsername(String username){
		return Admin.me().findFirst("SELECT * FROM t_auth_admin WHERE username=? AND enabled=1",username);
	}
	
	public Admin findById(int id){
		return Admin.me().findFirst("SELECT a.* FROM t_auth_admin a WHERE a.id=?",id);
	}
	
	public Page<Record> findByPage(int pageNum, int pageSize,String tel,String name,String jobs){
		//去掉超级管理员
		StringBuffer sqlExcept = new StringBuffer("FROM t_auth_admin A  WHERE id!=1 ");
		if(!StrKit.isBlank(tel)){
    		sqlExcept.append("AND A.phonenumber ="+"'"+tel+"'");
		}
	    if(!StrKit.isBlank(name)){
	    	sqlExcept.append("AND A.trueName ="+"'"+name+"'");
		}
	    //职位查询
/*	    if(!StrKit.isBlank(jobs)){
	    	sqlExcept = new StringBuffer("FROM  t_auth_roles S right join  t_auth_admin_roles Z on S.id= Z.roleid  left join t_auth_admin  A ON A.id=Z.userid  WHERE 1=1 ");
	    	String jobsList[]=jobs.split("','");
	    	for(int i=0;i<jobsList.length;i++){
	    		if(i==0){
	    			sqlExcept.append("AND S.id ="+jobsList[i]);
	    		}else{
	    			sqlExcept.append(" OR S.id ="+jobsList[i]);
	    		}	
	    	}
		}*/
		sqlExcept.append(" ORDER BY A.createDate DESC");
		return Db.paginate(pageNum, pageSize, "SELECT A.*", sqlExcept.toString());
	}
	
	public Admin findByUsername(String username){
		return Admin.me().findFirst("SELECT * FROM t_auth_admin WHERE username=?",username);
	}
	
	public List<Record> findRolesByUser(int userid){
		return Db.find("SELECT * FROM v_admin_roles WHERE userid=?",userid);
	}

	public List<Record> findExcelByPage(String beginDate,String endDate) {
		StringBuffer sBuffer = new StringBuffer("select * FROM t_obj_date td left join (select ifnull(count(*),0) as usNum,createDate from t_bus_user group by DATE_FORMAT(createDate,'%Y-%m-%d')) tu on td.RepDate = tu.createDate "
				+" where 1=1 " );
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
    		sBuffer.append("AND RepDate BETWEEN"+"'"+beginDate+"'"+ "AND "+"'" + endDate+"'");
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append("AND DATE_FORMAT(RepDate,'%Y-%m-%d')>="+"'"+beginDate+"'");
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append("AND DATE_FORMAT(td.RepDate,'%Y-%m-%d')<="+"'"+endDate+"'");
		}
		sBuffer.append(" order by RepDate desc");
		return Db.find(sBuffer.toString());
	}

	public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "日统计信息", titles, listz);
	}
}
